﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class AliquotaICMSCad
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            If (Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Alterar | Excluir
                CarregaDados()
            Else
                txtuforigem.Enabled = True
                txtufdestino.Enabled = True
                txtaliquota.Enabled = True
                txtreduzida.Enabled = True
                txtData.Enabled = True
            End If

        End If

    End Sub

    Private Sub CarregaDados()

        If (Session("strOperacao") = "E") Or (Session("stroperacao") = "A") Then ' Excluir
            txtuforigem.Enabled = False
            txtufdestino.Enabled = False
            txtaliquota.Enabled = False
            txtreduzida.Enabled = False
            txtData.Enabled = False
        End If

        txtuforigem.Text = Session("struforigem").ToString
        txtufdestino.Text = Session("strufdestino").ToString

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT aliquota, aliquotareduzida, dbo.FormataData(datavigencia) as Datavigencia" & _
                     " FROM AliquotaICMS" & _
                     " WHERE UFDESTINO = '" & Session("strufdestino").ToString & "' AND" & _
                     " UFORIGEM = '" & Session("struforigem").ToString & "';"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    txtaliquota.Text = theSqlDataReader(0)
                    txtreduzida.Text = theSqlDataReader(1)
                    txtData.Text = theSqlDataReader(2)
                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Function ValidaCampos() As Boolean

        ValidaCampos = True

        If (Trim(txtuforigem.Text) = "") Or Len(txtuforigem.Text) <> 2 Then
            ValidaCampos = False
            lblMsg.Text = "UF de origem invalida."
            Exit Function
        End If

        If (Trim(txtufdestino.Text) = "" Or Len(txtufdestino.Text) <> 2) Then
            ValidaCampos = False
            lblMsg.Text = "UF do destino deve ser preenchida."
            Exit Function
        End If

        If (Trim(txtaliquota.Text) = "") Then
            ValidaCampos = False
            lblMsg.Text = "Aliquota deve ser preenchida."
            Exit Function
        End If

        If (Trim(txtreduzida.Text) = "") Then
            ValidaCampos = False
            lblMsg.Text = "Aliquota reduzida deve ser preenchida."
            Exit Function
        End If

        If (Len(txtData.Text)) <> 10 Then
            ValidaCampos = False
            lblMsg.Text = "Tamanho da Data invalido."
            Exit Function
        End If

    End Function

    'Private Function VerificaDado(ByVal strConn As String) As Boolean

    '    'VerificaDado = False

    '    'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '    '    Dim theQueryString As String = "SELECT CodigoDoPaisISO" & _
    '    '             " FROM v_Holding" & _
    '    '             " WHERE CodigoDoPaisISO = '" & Session("CodigoDoPaisISO") & "'" & _
    '    '             " GROUP BY CodigoDoPaisISO"

    '    '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
    '    '    theSqlCommand.CommandText = theQueryString

    '    '    Try
    '    '        theSqlConnection.Open()
    '    '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '    '        If (theSqlDataReader.Read()) Then
    '    '            VerificaDado = True
    '    '            lblMsg.Text = "Exclusão não permitida, existe relação do País com Holding."
    '    '        End If
    '    '        theSqlDataReader.Close()
    '    '    Catch ex As Exception
    '    '        VerificaDado = True
    '    '        'theSqlDataReader.Close()
    '    '        lblMsg.Text = "Msg DB: " & ex.Message
    '    '    End Try

    '    'End Using

    'End Function

    'Private Function GetNextCategoriaFiscal() As Boolean

    '    GetNextCategoriaFiscal = False

    '    Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

    '        Try
    '            Dim theQueryString As String = ""

    '            theQueryString = "SELECT MAX(CodCategoriaFiscal) AS MAX_CodCategoriaFiscal" & _
    '                             " FROM categoriafiscal"

    '            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

    '            theSqlCommand.CommandText = theQueryString
    '            theSqlConnection.Open()

    '            Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

    '            Do While theSqlDataReader.Read()
    '                If Not IsDBNull(theSqlDataReader(0)) Then
    '                    txtCodCategoriaFiscal.Text = theSqlDataReader(0)
    '                    txtCodCategoriaFiscal.Text = (CInt(txtCodCategoriaFiscal.Text) + 1)
    '                End If
    '            Loop

    '            theSqlConnection.Close()

    '            GetNextCategoriaFiscal = True

    '        Catch ex As Exception
    '            theSqlConnection.Close()
    '            lblMsg.Text = "Msg DB: " & ex.Message
    '        End Try

    '    End Using

    'End Function

    Private Function Inclui(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nDataCreated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nDataCreated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nDataCreated)) Then
            nDataCreated = "'" & FormatMediumDate(nDataCreated) & "'"
        Else
            nDataCreated = "Null"
        End If

        Inclui = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "INSERT INTO aliquotaicms" & _
                     " (UForigem, ufDestino, aliquota, aliquotareduzida, DataVigencia," & _
                     " CreateUSer, Created)" & _
                     " VALUES ('" & Trim(txtuforigem.Text) & "','" & Trim(txtufdestino.Text) & "'," & " Replace('" & Trim(txtaliquota.Text) & "',',','.')," & " Replace('" & Trim(txtreduzida.Text) & "',',','.')," & FormatMediumDate(txtData.Text) & "," & _
                      " '" & Session("sNM_USERNICKNAME") & "', " & nDataCreated & ");"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                Inclui = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function Altera(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nDataCreated As String

        Altera = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nDataCreated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nDataCreated)) Then
            nDataCreated = "'" & FormatMediumDate(nDataCreated) & "'"
        Else
            nDataCreated = "Null"
        End If

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "UPDATE ICMS SET" & _
                     " PercentualICMS = replace('" & Trim(txtaliquota.Text) & "',',','.')," & _
                     " DataVigencia = " & FormatMediumDate(txtData.Text) & "," & _
                     " LastUser = 'Liion'," & _
                     " LastUpdated = " & nDataCreated & "" & _
                     " WHERE CodICMS = '" & Session("strCodICMS").ToString & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                Altera = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function Exclui(ByVal strConn As String) As Boolean

        Exclui = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM aliquotaicms " & _
                     "WHERE uforigem = '" & Trim(txtuforigem.Text) & "' AND" & _
                     " ufdestino = '" & Trim(txtufdestino.Text) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                Exclui = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""

        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (ValidaCampos() = True) Then
                Exit Sub
            End If

            If (CODExiste(ConnectionTest) = True) Then
                Exit Sub
            End If

            If (UFexiste(ConnectionTest, txtuforigem.Text) = True) Then
                Exit Sub
            End If

            If (UFexiste(ConnectionTest, txtufdestino.Text) = True) Then
                Exit Sub
            End If

            'If Not (GetNextCategoriaFiscal() = True) Then
            '    MensagemErro()
            '    Exit Sub
            'End If

            If Not (Inclui(ConnectionTest) = True) Then
                MensagemErro()
                Exit Sub
            End If


        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (ValidaCampos() = True) Then
                Exit Sub
            End If

            If Not (Altera(ConnectionTest) = True) Then
                MensagemErro()
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            'If (VerificaDado(ConnectionTest) = True) Then
            '    Exit Sub
            'End If

            If Not (Exclui(ConnectionTest) = True) Then
                MensagemErro()
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("AliquotaICMS.aspx")

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & strMM & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

    Private Sub MensagemErro()
        lblMsg.ForeColor = Drawing.Color.Red
        lblMsg.Text = "Suas informações não estão corretas ou a Aliquota ICMS já está cadastrada no sistema, corriga e tente novamente."
        txtaliquota.Text = ""
    End Sub

    Private Function CODExiste(ByVal strConn As String) As Boolean

        CODExiste = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT uforigem " & _
                     "FROM aliquotaicms " & _
                     "WHERE uforigem = '" & Trim(txtuforigem.Text) & "' AND" & _
                     " Datavigencia = '" & FormatMediumDate(txtData.Text) & "' AND" & _
                     " ufdestino = '" & Trim(txtufdestino.Text) & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    CODExiste = True
                    lblMsg.Text = "UF's já existem com a mesma data de vigencia."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                CODExiste = True
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function UFexiste(ByVal strconn As String, ByVal sigla As String) As Boolean

        UFexiste = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT sigladaUF " & _
                     "FROM UF " & _
                     "WHERE Sigladauf = '" & sigla & "';"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    UFexiste = False
                Else
                    UFexiste = True
                    lblMsg.Text = "UF não cadastrada."
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                UFexiste = True
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


    End Function
    
End Class
